package bigbanksystemserver;

import bigbanksystemserver.utils.ServerConnectionWrapper;
import java.io.IOException;
import java.net.DatagramPacket;
import java.net.DatagramSocket;
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Arrays;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

public class BigBankSystemServerThread extends Thread {

    private DatagramSocket servPort;
    private byte[] dataReceived;
    private byte[] dataSent;
    private DatagramPacket packetReceived;
    private DatagramPacket packetSent;
    private Connection dbConn;
    private javax.swing.JTextArea outputConsole;
    private String branch;
    private String ipAddress;
    private ServerConnectionWrapper wrapper;
    private String requestBranch;
    private int clientPort;
    private InetAddress clientIP;
    String neighborIP;
    int neighborPort;

    public BigBankSystemServerThread(javax.swing.JTextArea outputConsole, String branch, int portNo) throws IOException {
        this.outputConsole = outputConsole;
        String[] branchInfo = branch.split(" ");
        this.branch = branchInfo[0];
        this.ipAddress = branchInfo[1].substring(1, branchInfo[1].length() - 1);
        servPort = new DatagramSocket(portNo);
        dataReceived = new byte[65536];
        dataSent = new byte[65536];
        packetReceived = new DatagramPacket(dataReceived, dataReceived.length);
        packetSent = new DatagramPacket(dataSent, dataSent.length);
        outputToConsole("Server started.");
        establishDBConnection(ipAddress);
        clientPort = 0;
        neighborPort = 0;
        requestBranch = this.branch;
    }

    public void run() {
        while (!Thread.currentThread().isInterrupted()) {

            try {
                servPort.receive(packetReceived);
                String clientMsg = new String(packetReceived.getData(), 0, packetReceived.getLength(), "UTF-8");
                clientMsg = clientMsg.trim();
                String[] request = clientMsg.split("&");
                boolean flag = false;
                String result = "";
                System.out.println("client msg : " + clientMsg);
                wrapper = new ServerConnectionWrapper();
                setNeighborIPandPort();
                if (request[request.length - 1].contains("branch")) {
                    if (request[request.length - 1].split(":")[1].equals(branch)) {
                        flag = true;
                    } else {
                        requestBranch = request[request.length - 1].split(":")[1];
                    }
                } else {
                    clientPort = packetReceived.getPort();
                    clientIP = packetReceived.getAddress();
                }
                if (flag) {
                    if (request[0].equals("Success")) {
                        String sendData = "";
                        for (int i = 1; i < request.length - 1; i++) {
                            sendData += request[i] + "&";
                        }
                        System.out.println(sendData);
                        sendPacket(sendData.getBytes());
                    } else {
                        sendPacket(request[1].getBytes());
                    }
                } else if (!(request[0].equalsIgnoreCase("Success") || request[0].equalsIgnoreCase("Failed") || request[0].contains("Error"))) {
                    if (request[0].equalsIgnoreCase("LOGIN")) {
                        login(request[1], request[2]);
                    } else if (request[0].equalsIgnoreCase("QUERY")) {
                        result = query(request[1]);
                    } else if (request[0].equalsIgnoreCase("REGISTER")) {
                        registerAccount(request[1], request[2], request[3], request[4], request[5], request[6],
                                request[7], request[8], request[9], request[10], request[11], request[12],
                                request[13], request[14], request[15], request[16]);

                    } else if (request[0].equalsIgnoreCase("WITHDRAW")) {
                        result = withdraw(request[1], Double.parseDouble(request[2]));

                    } else if (request[0].equalsIgnoreCase("DEPOSIT")) {
                        result = deposit(request[1], Double.parseDouble(request[2]));

                    } else if (request[0].equalsIgnoreCase("TRANSFER")) {
                        result = transfer(request[1], request[2], Double.parseDouble(request[3]));
                    } else if (request[0].equalsIgnoreCase("CHECKLOAN")) {
                        result = checkLoan(request[1]);

                    } else if (request[0].equalsIgnoreCase("APPLYLOAN")) {
                        result = applyLoan(Double.parseDouble(request[1]), Integer.parseInt(request[2]), Double.parseDouble(request[3]),
                                Double.parseDouble(request[4]), Double.parseDouble(request[5]), request[6], request[7]);

                    } else if (request[0].equalsIgnoreCase("PAYLOAN")) {
                        result = payLoan(Double.parseDouble(request[1]), request[2]);

                    } else if (request[0].equalsIgnoreCase("HISTORY")) {
                        result = history(request[1]);

                    }
                    System.out.println("sdfghjdszxdcfvgbn +++ "+result);
                    if (!(request.length < 2)) {
                        if (branch.equals(requestBranch) && request[1].substring(0, 2).equals(branch) && !request[0].equalsIgnoreCase("TRANSFER")) {
                            String sendData = "";
                            for (int i = 1; i < result.split("&").length - 1; i++) {
                                sendData += result.split("&")[i] + "&";
                            }
                            if (!sendData.trim().equals("")) {
                                System.out.println("1 " + sendData.substring(0, sendData.length() - 1));
                                sendPacket(sendData.substring(0, sendData.length() - 1).getBytes());
                            }
                        } else {
                            System.out.println("2 " + result + " " + neighborIP + " " + neighborPort);
                            wrapper.send(result, InetAddress.getByName(neighborIP), neighborPort);
                        }
                    }

                } else {
                    System.out.println("3" + clientMsg.toString());
                    wrapper.send(clientMsg, InetAddress.getByName(neighborIP), neighborPort);

                }
            } catch (IOException ex) {
            } catch (SQLException ex) {
                Logger.getLogger(BigBankSystemServerThread.class.getName()).log(Level.SEVERE, null, ex);
            }

        }
    }

    private void establishDBConnection(String ipAddress) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            dbConn = DriverManager.getConnection("jdbc:mysql://" + ipAddress + ":3306/bankdb", "Jay", "12345678");
            outputToConsole("Connected to Branch " + branch + " database at " + ipAddress + ".");
        } catch (Exception ex) {
            ex.printStackTrace();
            outputToConsole("Error in connecting to database!");
        }
    }

    private void sendPacket(byte[] sendData) {
        try {
            packetSent.setData(sendData);
            packetSent.setAddress(clientIP);
            packetSent.setPort(clientPort);
            servPort.send(packetSent);
            outputToConsole("Packet Sent.");
        } catch (Exception ex) {
            outputToConsole("Error in sending packet!");
        }
    }

    private void login(String staffID, String password) {
        String sendData = "";
        try {

            PreparedStatement stmtSearch = dbConn.prepareStatement("SELECT * FROM staff WHERE staffID = ?");
            stmtSearch.setString(1, staffID);
            ResultSet rsSearch = stmtSearch.executeQuery();
            if (rsSearch.next()) {
                if (!rsSearch.getString("branchID").equals(branch)) {
                    outputToConsole(staffID + " logged in failed.");
                    String errorMsg = "Error: Invalid Branch !\n(Staff Only Accessible to Respective Branch)";
                    sendData = errorMsg;
                    sendPacket(errorMsg.getBytes());
                } else if (rsSearch.getString("password").equals(password)) {
                    outputToConsole(staffID + " logged in successfully.");
                    sendData = rsSearch.getString("staffName");
                    sendPacket(sendData.getBytes());
                } else {
                    outputToConsole(staffID + " logged in failed.");
                    String errorMsg = "Error: Incorrect Password !";
                    sendData = errorMsg;
                    sendPacket(errorMsg.getBytes());
                }
            } else {
                outputToConsole(staffID + " logged in failed.");
                String errorMsg = "Error: Invalid Staff ID !";
                sendData = errorMsg;
                sendPacket(errorMsg.getBytes());
            }
        } catch (Exception ex) {
            outputToConsole("Failed&Error in retrieving staff record!");
        }
    }

    private void registerAccount(String accountNo, String accountType, String accountBalance, String registerDate,
            String holderName, String icNo, String gender, String dob, String race, String salary, String phoneNo,
            String address, String postalCode, String city, String state, String branchID) {

        try {
            String insertSQL = "INSERT INTO account(accountNo,accountType,accountBalance,registerDate,holderName,icNo,gender,"
                    + "dob,race,salary,phoneNo,address,postalCode,city,state,branchID) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement stmtInsert = dbConn.prepareStatement(insertSQL);

            stmtInsert.setString(1, accountNo);
            stmtInsert.setString(2, accountType);
            stmtInsert.setDouble(3, Double.parseDouble(accountBalance));
            stmtInsert.setString(4, registerDate);
            stmtInsert.setString(5, holderName);
            stmtInsert.setString(6, icNo);
            stmtInsert.setString(7, gender);
            stmtInsert.setString(8, dob);
            stmtInsert.setString(9, race);
            stmtInsert.setDouble(10, Double.parseDouble(salary));
            stmtInsert.setString(11, phoneNo);
            stmtInsert.setString(12, address);
            stmtInsert.setString(13, postalCode);
            stmtInsert.setString(14, city);
            stmtInsert.setString(15, state);
            stmtInsert.setString(16, branchID);

            stmtInsert.executeUpdate();

            outputToConsole("New Account(" + accountNo + ") added successfully.");
            String sendData = "Success&New Account Added Successfully.";
            sendPacket(sendData.getBytes());

        } catch (Exception ex) {
            outputToConsole("Error in adding new account!");
            String sendData = "Error: Adding New Account Failed !";
            sendPacket(sendData.getBytes());
        }
    }

    private String query(String acnt) {
        String sendData = "";
        try {
            PreparedStatement stmtSearch = dbConn.prepareStatement("SELECT * FROM account WHERE accountNo = ?");
            stmtSearch.setString(1, acnt);
            ResultSet rsSearch = stmtSearch.executeQuery();
            if (acnt.substring(0, 2).equals(branch)) {
                if (rsSearch.next()) {
                    String holderName = rsSearch.getString("holderName");
                    String icNo = rsSearch.getString("icNo");
                    String accountType = rsSearch.getString("accountType");
                    double accountBalance = rsSearch.getDouble("accountBalance");

                    outputToConsole("Account details of " + acnt + " retrieved successfully.");
                    sendData = "Success&" + holderName + "&" + icNo + "&" + accountType + "&" + accountBalance;
                } else {
                    outputToConsole("Retrieving account details of " + acnt + " failed.");
                    sendData = "Failed&Error: Invalid Account Number !";
                }
            } else {
                outputToConsole("Acc \'" + acnt + "\' not belongs to this branch, sending to next branch.");
                sendData = "QUERY&" + acnt;
            }
        } catch (Exception ex) {
            outputToConsole("Error in retrieving account record!");
        }
        return sendData + "&branch:" + requestBranch;
    }

    private String withdraw(String acnt, double amt) {
        String sendData = "";
        try {
            PreparedStatement stmtSearch = dbConn.prepareStatement("SELECT accountBalance FROM account WHERE accountNo = ?");
            stmtSearch.setString(1, acnt);
            ResultSet rsSearch = stmtSearch.executeQuery();
            double balance = 0;
            if (acnt.substring(0, 2).equals(branch)) {
                if (rsSearch.next()) {
                    balance = rsSearch.getDouble("accountBalance");
                }


                String updateSQL = "UPDATE account SET accountBalance = ? WHERE accountNo = ?";
                PreparedStatement stmtUpdate = dbConn.prepareStatement(updateSQL);

                balance -= amt;

                stmtUpdate.setDouble(1, balance);
                stmtUpdate.setString(2, acnt);
                stmtUpdate.executeUpdate();

                outputToConsole("Withdrawal of " + acnt + " Successful.");
                sendData = "Success&RM" + String.format("%.2f", amt) + " withdrawn successfully.";
                transaction("DEBIT", "Withdrawal of RM" + String.format("%.2f", amt), amt, acnt);
            } else {
                outputToConsole("Acc \'" + acnt + "\' not belongs to this branch, sending to next branch.");
                sendData = "WITHDRAW&" + acnt + "&" + amt;
            }
        } catch (Exception ex) {
            outputToConsole("Error in withdrawal!");
            sendData = "Failed&Error: Withdrawal Failed !";
        }
        return sendData + "&branch:" + requestBranch;
    }

    private String deposit(String acnt, double amt) {
        String sendData = "";
        try {
            PreparedStatement stmtSearch = dbConn.prepareStatement("SELECT accountBalance FROM account WHERE accountNo = ?");
            stmtSearch.setString(1, acnt);
            ResultSet rsSearch = stmtSearch.executeQuery();
            double balance = 0;
            if (acnt.substring(0, 2).equals(branch)) {
                if (rsSearch.next()) {
                    balance = rsSearch.getDouble("accountBalance");
                }


                String updateSQL = "UPDATE account SET accountBalance = ? WHERE accountNo = ?";
                PreparedStatement stmtUpdate = dbConn.prepareStatement(updateSQL);

                balance += amt;

                stmtUpdate.setDouble(1, balance);
                stmtUpdate.setString(2, acnt);
                stmtUpdate.executeUpdate();

                outputToConsole("Deposit of " + acnt + " Successful.");
                sendData = "Success&RM" + String.format("%.2f", amt) + " deposit successfully.";
                transaction("CREDIT", "Deposit of RM" + String.format("%.2f", amt), amt, acnt);
            } else {
                outputToConsole("Acc \'" + acnt + "\' not belongs to this branch, sending to next branch.");
                sendData = "DEPOSIT&" + acnt + "&" + amt;
            }
        } catch (Exception ex) {
            outputToConsole("Error in deposit!");
            sendData = "Failed&Error: Deposit Failed !";
        }
        System.out.println("deposit " + sendData);
        return sendData + "&branch:" + requestBranch;
    }

    private String checkLoan(String acnt) {
        String sendData = "";
        try {
            PreparedStatement stmtSearch = dbConn.prepareStatement("SELECT * FROM account WHERE accountNo = ?");
            stmtSearch.setString(1, acnt);
            ResultSet rsSearch = stmtSearch.executeQuery();
            if (acnt.substring(0, 2).equals(branch)) {
                if (rsSearch.next()) {
                    String holderName = rsSearch.getString("holderName");
                    String icNo = rsSearch.getString("icNo");
                    String accountType = rsSearch.getString("accountType");
                    double accountBalance = rsSearch.getDouble("accountBalance");
                    double salary = rsSearch.getDouble("salary");

                    outputToConsole("Account details of " + acnt + " retrieved successfully.");

                    stmtSearch = dbConn.prepareStatement("SELECT * FROM loan WHERE accountNo = ? AND status = ?");
                    stmtSearch.setString(1, acnt);
                    stmtSearch.setString(2, "Available");
                    rsSearch = stmtSearch.executeQuery();

                    if (rsSearch.next()) {
                        String loanID = rsSearch.getString("loanID");
                        double loanAmount = rsSearch.getDouble("loanAmount");
                        int term = rsSearch.getInt("term");
                        double rate = rsSearch.getDouble("rate");
                        double monthlyRepayment = rsSearch.getDouble("monthlyRepayment");
                        double remainingAmount = rsSearch.getDouble("remainingAmount");
                        String applicationDate = rsSearch.getString("applicationDate");

                        outputToConsole("Loan details of " + acnt + " retrieved successfully.");
                        sendData = "Success&" + holderName + "&" + icNo + "&" + accountType + "&" + accountBalance
                                + "&" + salary + "&" + loanID + "&" + loanAmount + "&" + term + "&" + rate + "&"
                                + monthlyRepayment + "&" + remainingAmount + "&" + applicationDate;
                    } else {
                        outputToConsole("Retrieving loan details of " + acnt + " failed.");
                        String errorMsg = "Error: No Loan Record Found !&" + holderName + "&" + icNo + "&" + accountBalance;
                        sendData = "Failed&" + errorMsg;
                    }

                } else {
                    outputToConsole("Retrieving account details of " + acnt + " failed.");
                    String errorMsg = "Error: Invalid Account Number !";
                    sendData = "Failed&" + errorMsg;
                }
            } else {
                outputToConsole("Acc \'" + acnt + "\' not belongs to this branch, sending to next branch.");
                sendData = "CHECKLOAN&" + acnt;
            }
        } catch (Exception ex) {
            System.out.println(ex);
            outputToConsole("Error in retrieving account record!");
        }
        return sendData + "&branch:" + requestBranch;
    }

    private String applyLoan(double loanAmount, int term, double rate, double monthlyRepayment, double remainingAmount, String applicationDate, String acnt) {
        String newLoanID = "";
        String sendData = "";
        try {
            PreparedStatement stmtSearch = dbConn.prepareStatement("SELECT * FROM loan");
            ResultSet rsSearch = stmtSearch.executeQuery();
            if (acnt.substring(0, 2).equals(branch)) {
                if (!rsSearch.next()) {
                    newLoanID = "L0001";
                } else {
                    if (rsSearch.last()) {
                        String lastLoanID = rsSearch.getString("loanID");
                        int temp = Integer.parseInt(lastLoanID.substring(1)) + 1;
                        newLoanID = "L" + String.format("%04d", temp);
                    }
                }

                String insertSQL = "INSERT INTO loan(loanID,loanAmount,term,rate,monthlyRepayment,remainingAmount,applicationDate,status,accountNo)"
                        + " VALUES(?,?,?,?,?,?,?,?,?)";
                PreparedStatement stmtInsert = dbConn.prepareStatement(insertSQL);

                stmtInsert.setString(1, newLoanID);
                stmtInsert.setDouble(2, loanAmount);
                stmtInsert.setInt(3, term);
                stmtInsert.setDouble(4, rate);
                stmtInsert.setDouble(5, monthlyRepayment);
                stmtInsert.setDouble(6, remainingAmount);
                stmtInsert.setString(7, applicationDate);
                stmtInsert.setString(8, "Available");
                stmtInsert.setString(9, acnt);

                stmtInsert.executeUpdate();

                outputToConsole("Loan application of " + acnt + " successful.");
                sendData = "Success&Loan application successful.";
            } else {
                outputToConsole("Acc \'" + acnt + "\' not belongs to this branch, sending to next branch.");
                sendData = "APPLYLOAN&" + acnt;
            }
        } catch (Exception ex) {
            outputToConsole("Loan application of " + acnt + " failed.");
            sendData = "Failed&Error: Loan application failed !";
        }
        return sendData + "&branch:" + requestBranch;
    }

    private String payLoan(double remainingAmount, String acnt) {
        String sendData = "";
        try {
            PreparedStatement stmtSearch = dbConn.prepareStatement("SELECT * FROM loan WHERE accountNo = ?");
            stmtSearch.setString(1, acnt);
            ResultSet rsSearch = stmtSearch.executeQuery();
            String loanID = "";
            if (acnt.substring(0, 2).equals(branch)) {
                if (rsSearch.next()) {
                    loanID = rsSearch.getString("loanID");

                    PreparedStatement stmtUpdate = dbConn.prepareStatement("UPDATE loan SET remainingAmount = ?, status = ? WHERE loanID = ?");
                    stmtUpdate.setString(1, remainingAmount + "");
                    stmtUpdate.setString(2, remainingAmount <= 0 ? "Paid Off" : "Available");
                    stmtUpdate.setString(3, loanID);
                    stmtUpdate.executeUpdate();

                    outputToConsole("Loan repayment of " + acnt + " successful.");
                    sendData = "Success&Loan repayment successful.";
                }
            } else {
                outputToConsole("Acc \'" + acnt + "\' not belongs to this branch, sending to next branch.");
                sendData = "PAYLOAN&" + acnt;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            outputToConsole("Loan repayment of " + acnt + " failed.");
            sendData = "Failed&Error: Loan repayment failed !";
        }
        return sendData + "&branch:" + requestBranch;
    }

    private void transaction(String transactionType, String transactionDetail, double amount, String acnt) {
        String newTransactionID = "";
        SimpleDateFormat df = new SimpleDateFormat("dd-MM-yyyy");
        SimpleDateFormat tf = new SimpleDateFormat("hh:mm:ss");

        try {
            PreparedStatement stmtSearch = dbConn.prepareStatement("SELECT * FROM transaction");
            ResultSet rsSearch = stmtSearch.executeQuery();

            if (!rsSearch.next()) {
                newTransactionID = "T0001";
            } else {
                if (rsSearch.last()) {
                    String lastTransactionID = rsSearch.getString("transactionID");
                    int temp = Integer.parseInt(lastTransactionID.substring(1)) + 1;
                    newTransactionID = "T" + String.format("%04d", temp);
                }
            }

            String insertSQL = "INSERT INTO transaction(transactionID,transactionType,transactionDetail,transactionDate,transactionTime,amount,accountNo)"
                    + " VALUES(?,?,?,?,?,?,?)";
            PreparedStatement stmtInsert = dbConn.prepareStatement(insertSQL);

            stmtInsert.setString(1, newTransactionID);
            stmtInsert.setString(2, transactionType);
            stmtInsert.setString(3, transactionDetail);
            stmtInsert.setString(4, df.format(new Date()));
            stmtInsert.setString(5, tf.format(new Date()));
            stmtInsert.setDouble(6, amount);
            stmtInsert.setString(7, acnt);

            stmtInsert.executeUpdate();

            outputToConsole("Transaction record of " + acnt + " added successfully.");

        } catch (Exception ex) {
            outputToConsole("Adding transaction record of " + acnt + " failed.");
        }
    }

    public String history(String acnt) {
        String sendData = "";
        try {
            PreparedStatement stmtSearch = dbConn.prepareStatement("SELECT * FROM transaction WHERE accountNo = ?");
            stmtSearch.setString(1, acnt);
            ResultSet rsSearch = stmtSearch.executeQuery();
            if (acnt.substring(0, 2).equals(branch)) {
                while (rsSearch.next()) {
                    String transactionID = rsSearch.getString("transactionID");
                    String transactionType = rsSearch.getString("transactionType");
                    String transactionDetail = rsSearch.getString("transactionDetail");
                    String transactionDate = rsSearch.getString("transactionDate");
                    String transactionTime = rsSearch.getString("transactionTime");
                    double amount = rsSearch.getDouble("amount");

                    if (rsSearch.isFirst()) {
                        outputToConsole("Transaction records of " + acnt + " retrieved successfully.");

                        sendData += transactionID + "&" + transactionDate + "&" + transactionTime + "&"
                                + transactionType + "&" + amount + "&" + transactionDetail + ",";

                    } else if (rsSearch.isLast()) {
                        sendData += transactionID + "&" + transactionDate + "&" + transactionTime + "&"
                                + transactionType + "&" + amount + "&" + transactionDetail;
                    } else {
                        sendData += transactionID + "&" + transactionDate + "&" + transactionTime + "&"
                                + transactionType + "&" + amount + "&" + transactionDetail + ",";
                    }
                }
                if (!sendData.equals("")) {
                    sendData = "Success&" + sendData;
                } else {
                    sendData = "Failed&Error: No Record Found !";
                }
            } else {
                outputToConsole("Acc \'" + acnt + "\' not belongs to this branch, sending to next branch.");
                sendData = "HISTORY&" + acnt;
            }
        } catch (Exception ex) {
            outputToConsole("Error in retrieving transaction records of " + acnt + "!");
        }
        return sendData + "&branch:" + requestBranch;
    }

    public void stopServer() {
        try {
            dbConn.close();
            servPort.close();
            outputToConsole("Server stopped.");
        } catch (Exception ex) {
        }

        stop();
    }

    private void outputToConsole(String output) {
        SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy  HH:mm:ss");

        if (!outputConsole.getText().equals("")) {
            outputConsole.append("\n" + df.format(new Date()) + "     " + output);
        } else {
            outputConsole.append(df.format(new Date()) + "     " + output);
        }
    }

    private void setNeighborIPandPort() {
        String neighborBranch = wrapper.whoNeighbors(branch);
        neighborIP = getNeighborIP(neighborBranch);
        if (neighborBranch.equals("01")) {
            neighborPort = 1001;
        } else if (neighborBranch.equals("02")) {
            neighborPort = 1002;
        } else if (neighborBranch.equals("03")) {
            neighborPort = 1003;
        } else if (neighborBranch.equals("04")) {
            neighborPort = 1004;
        } else if (neighborBranch.equals("05")) {
            neighborPort = 1005;
        }
    }

    private String getNeighborIP(String neighborBranch) {
        PreparedStatement stmtSearch;
        try {
            stmtSearch = dbConn.prepareStatement("SELECT * FROM branch WHERE branchID = ?");
            stmtSearch.setString(1, neighborBranch);
            ResultSet rsSearch = stmtSearch.executeQuery();

            if (rsSearch.next()) {
                String neighborIP = rsSearch.getString("ipAddress");
                outputToConsole("Neighbor IP address : " + neighborIP);
                return neighborIP;
            } else {
                outputToConsole("Error in retrieving Neighbor IP address!");
                return "Error";
            }
        } catch (SQLException ex) {
            Logger.getLogger(BigBankSystemServerThread.class.getName()).log(Level.SEVERE, null, ex);
            return "Error";
        }
    }

    private String transfer(String sourceaccountno, String destinationaccountno, double amt) throws SQLException {
        String sendData = "";
        try {
            PreparedStatement stmtSearch = dbConn.prepareStatement("SELECT * FROM transaction WHERE accountNo = ?");
            stmtSearch.setString(1, sourceaccountno);
            ResultSet rsSearch = stmtSearch.executeQuery();
            if (sourceaccountno.substring(0, 2).equals(branch)) {
                if (rsSearch.next()) {
                    if (withdraw(sourceaccountno, amt).split("&")[0].equals("Success")) {
                        sendData = "TRANSFER&";
                    } else {
                        sendData = "Failed&Error in transfering fund.";
                    }
                }
            }
            System.out.println("1dd");
            stmtSearch = dbConn.prepareStatement("SELECT * FROM transaction WHERE accountNo = ?");
            stmtSearch.setString(1, destinationaccountno);
            rsSearch = stmtSearch.executeQuery();
            System.out.println(destinationaccountno + "   sd " + branch);
            if (destinationaccountno.substring(0, 2).equals(branch)) {
                System.out.println("3333ddd");
                if (rsSearch.next()) {
                    System.out.println("2dd");
                    if (deposit(destinationaccountno, amt).split("&")[0].equals("Success")) {
                        System.out.println("Successssssssssssssss");
                        sendData = "Success";
                    }
                } else {
                    sendData = "Failed&Error in transfering fund.";
                }
            }

        } catch (SQLException ex) {
            sendData = "Failed&Error in transfering fund.";
        }
        sendData = sendData + "&" + sourceaccountno + "&" + destinationaccountno + "&" + amt;
        System.out.println("ffffffffffssddfsdf " + sendData);
        return sendData + "&branch:" + requestBranch;
    }
}
